package opdr5a;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Random;


public class Console {
	private Connection connection;
	
	public Console() {
		try {
			connectionSetup();
			start();
		} catch (InstantiationException | SQLException | IllegalAccessException
				| ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Maak verbinding met de database
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	private void connectionSetup() throws InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection("jdbc:mysql://localhost/funda_site_2", "root", "");
	}
	
	
	private int getRandomMedewerker(int mkid) throws SQLException {
		String sql = "SELECT a.MKMDWID FROM medewerkers AS a, mkantoor_medewerkers AS b WHERE  a.MKMDWID = b.MKMDWID " +
				"AND b.mkid=" + mkid;
		Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
		stmt.execute(sql);
		//System.out.println(sql);
		ResultSet rs = stmt.getResultSet();
		rs.last();
		int count = rs.getRow();
		int row = (int) Math.ceil(count * Math.random());
		rs.absolute(row);
		int id = rs.getInt(1);
		stmt.close();
		return id;
	}
	
	private void start() throws SQLException {
		String sql = "SELECT COUNT(*) FROM wo";
		Statement stmt = connection.createStatement();
		stmt.execute(sql);
		ResultSet rs = stmt.getResultSet();
		rs.next();
		int count = rs.getInt(1);
		int limit = 500;
		for(int i = 0; i < count; i++) {
			iterateWoningen(i * limit, limit);
			//System.out.println(" woning " + i);
		}
	}
	
	private void iterateWoningen(int page, int limit) throws SQLException {
		String sql = "SELECT wo.woid, wo.mkid FROM wo LIMIT " + page + ", " + limit;
		Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
		stmt.execute(sql);
		//System.out.println(sql);
		ResultSet rs = stmt.getResultSet();
		while(rs.next()) {
			int woid = rs.getInt(1);
			int mkid = rs.getInt(2);
			int mkmdwid = getRandomMedewerker(mkid);
			insert(woid, mkmdwid);
		}
		stmt.close();
	}
	
	private void insert(int woid, int mkmdwid) throws SQLException {
		
		String sql = "INSERT INTO wo_mkmdwid (woid, MKMDWID) " +
				"values (" + woid + ", " + mkmdwid + ")" +
				" on duplicate key update woid=values(woid), MKMDWID=values(MKMDWID)";
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sql);
		stmt.close();
		//System.out.println(sql);
	}

}
